﻿// -----------------------------------------------------------------------------------------------------
// <copyright file="ImportRR.cs" company="www.swyx-forum.com">
//     (c) Martin Hüser
// </copyright>
// <summary>Type ImportRR which implements SwyxWare routing record import/export functionality</summary>
// -----------------------------------------------------------------------------------------------------
namespace IpPbxImportRR
{
    using System;
    using System.Data.OleDb;
    using System.Diagnostics;
    using System.IO;
    using System.Reflection;
    using System.Runtime.InteropServices;
    using System.Text;
    using RJH.CommandLineHelper;
    using SWConfigDataClientLib.Proxies.Admin;
    using SWConfigDataClientLib.Proxies.PortBundle;
    using SWConfigDataSharedLib.Exceptions;
    using System.Globalization;

    /// <summary>
    /// Implements SwyxWare routing record import
    /// </summary>
    internal class ImportRR : IDisposable
    {   
        /// <summary>
        /// trace source object for diagnostic output
        /// </summary>
        private static TraceSource ts = new TraceSource("IpPbxImportRR");

        #region Command line arguments
        /// <summary>
        /// command line parameter defining server to connect to. 
        /// Can be a server name or IP address
        /// </summary>
        private string cmdLineServerName;

        /// <summary>
        /// command line parameter defining user name for connection to CDS
        /// </summary>
        private string cmdLineUsername;

        /// <summary>
        /// command line parameter defining password for connection to CDS
        /// </summary>
        private string cmdLinePassword;

        /// <summary>
        /// command line parameter defining routing record definition file to import
        /// </summary>
        private string cmdLineFileToProcess;

        /// <summary>
        /// command line parameter "show help"
        /// </summary>
        private bool cmdLineShowHelp;

        /// <summary>
        /// CDSTools instance for accessing ConfigDataStore
        /// </summary>
        private CDSTools cdsTools;

        /// <summary>
        /// Command line parameter defining that routing records should be imported
        /// </summary>
        private bool cmdLineImport;

        /// <summary>
        /// command line parameter defining that routing records should be exported
        /// </summary>
        private bool cmdLineExport;

        /// <summary>
        /// command line paramter defining that existng routing records are to be replace during import
        /// </summary>
        private bool cmdLineReplace;

        /// <summary>
        /// flag to indicate if this.Dispose() has been explicitely called
        /// </summary>
        private bool disposed = false;

        /// <summary>
        /// Creates an ImportRR object.
        /// </summary>
        public ImportRR()
        {
        }

        /// <summary>
        /// lookup method delegate 
        /// </summary>
        /// <param name="name">Name to lookup</param>
        /// <returns>ID of the name</returns>
        private delegate int LookupMethod(string name);

        /// <summary>
        /// Gets or sets a value indicating whether to import routing records
        /// </summary>
        [CommandLineSwitch("import", "import routing records from file to SwyxServer")]
        [CommandLineAlias("i")]
        [ComVisible(false)]
        public bool CmdLineImport
        {
            get 
            { 
                return this.cmdLineImport; 
            }
            
            set 
            { 
                this.cmdLineImport = value; 
            }
        }

        /// <summary>
        /// Gets or sets a value indicating whether to export routing records
        /// </summary>
        [CommandLineSwitch("export", "export routing records from SwyxServer to file")]
        [CommandLineAlias("e")]
        [ComVisible(false)]
        public bool CmdLineExport
        {
            get 
            { 
                return this.cmdLineExport; 
            }
            
            set 
            { 
                this.cmdLineExport = value; 
            }
        }

        /// <summary>
        /// Gets or sets command line parameter value specifying the servername to connec to
        /// </summary>
        [CommandLineSwitch("server", "IpPbxServer to use (localhost is used when not specified)")]
        [CommandLineAlias("s")]
        [ComVisible(false)]
        public string CmdLineServerName
        {
            get 
            { 
                return this.cmdLineServerName; 
            }
            
            set 
            { 
                this.cmdLineServerName = value; 
            }
        }

        /// <summary>
        /// Gets or sets command line parameter value specifying the excel file to process
        /// </summary>
        [CommandLineSwitch("file", "<filename>", "process <filename>. Can be .xls, .xlsx or .csv")]
        [CommandLineAlias("f")]
        [ComVisible(false)]
        public string CmdLineFileToProcess
        {
            get
            {
                return this.cmdLineFileToProcess;
            }

            set
            {
                string extension = Path.GetExtension(value);
                if (string.IsNullOrEmpty(extension))
                {
                    this.cmdLineFileToProcess = value + ".xlsx";
                }
                else if (string.Compare(extension, ".xls", StringComparison.OrdinalIgnoreCase) == 0
                    || string.Compare(extension, ".xlsx", StringComparison.OrdinalIgnoreCase) == 0
                    || string.Compare(extension, ".csv", StringComparison.OrdinalIgnoreCase) == 0)
                {
                    this.cmdLineFileToProcess = value;
                }
                else
                {
                    throw new ArgumentException("Expected a .xls, .xlsx or .csv file");
                }
            }
        }

        /// <summary>
        /// Gets or sets command line parameter value specifying the password to connect to the server
        /// </summary>
        [CommandLineSwitch("username", "<username>", "user name to login to SwyxServer. Uses current windows account if not specified")]
        [CommandLineAlias("u")]
        [ComVisible(false)]
        public string CmdLineUsername
        {
            get 
            { 
                return this.cmdLineUsername; 
            }
            
            set 
            { 
                this.cmdLineUsername = value; 
            }
        }

        /// <summary>
        /// Gets or sets command line parameter value specifying the username to connect to the server
        /// </summary>
        [CommandLineSwitch("password", "<password>", "password for login to SwyxServer. Required when username is specified")]
        [CommandLineAlias("p")]
        [ComVisible(false)]
        public string CmdLinePassword
        {
            get 
            { 
                return this.cmdLinePassword; 
            }
            
            set 
            { 
                this.cmdLinePassword = value; 
            }
        }

        /// <summary>
        /// Gets or sets a value indicating whether to show command line help
        /// </summary>
        [CommandLineSwitch("?", "Show help")]
        [ComVisible(false)]
        public bool CmdLineShowHelp
        {
            get 
            { 
                return this.cmdLineShowHelp; 
            }
            
            set 
            { 
                this.cmdLineShowHelp = value; 
            }
        }

        /// <summary>
        /// Gets or sets a value indicating whether to replace routing records during import or just adding them
        /// </summary>
        [CommandLineSwitch("replace", "Replace existing records with Description 'ImportRR*'. Adds them when not specified.")]
        [CommandLineAlias("r")]
        [ComVisible(false)]
        public bool CmdLineReplace
        {
            get 
            { 
                return this.cmdLineReplace; 
            }
            
            set 
            { 
                this.cmdLineReplace = value; 
            }
        }
        #endregion

        /// <summary>
        /// Called to dispose unmanaged resources. If you do not call it explicitely, finalizer
        /// will do it at an arbitrary time
        /// </summary>
        public void Dispose()
        {
            this.Dispose(true);

            // This object will be cleaned up by the Dispose method.
            // Therefore, you should call GC.SupressFinalize to
            // take this object off the finalization queue
            // and prevent finalization code for this object
            // from executing a second time.
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// Get "database connection" to excel file
        /// </summary>
        /// <param name="filename">excel file to open</param>
        /// <returns>OleDbConnection object</returns>
        internal static OleDbConnection GetConnection(string filename)
        {
            StringBuilder sb = new StringBuilder();
            if (string.Compare(Path.GetExtension(filename), ".xls", StringComparison.OrdinalIgnoreCase) == 0)
            {
                sb.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename);
                sb.Append(";Data Source=" + filename);
                sb.Append(";Extended Properties=");
                sb.Append(Convert.ToChar(34));
                sb.Append("Excel 8.0;HDR=Yes;IMEX=2");
                sb.Append(Convert.ToChar(34));
            }
            else if (string.Compare(Path.GetExtension(filename), ".xlsx", StringComparison.OrdinalIgnoreCase) == 0)
            {
                sb.Append(@"Provider=Microsoft.ACE.OLEDB.12.0");
                sb.Append(";Data Source=" + filename);
                sb.Append(";Extended Properties=");
                sb.Append(Convert.ToChar(34));
                sb.Append("Excel 12.0;HDR=Yes");
                sb.Append(Convert.ToChar(34));
            }

            if (string.Compare(Path.GetExtension(filename), ".csv", StringComparison.OrdinalIgnoreCase) == 0)
            {
                string filePath = Path.GetDirectoryName(Path.GetFullPath(filename));
                sb.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath);
                sb.Append(";Data Source=" + filePath);
                sb.Append(";Extended Properties=");
                sb.Append(Convert.ToChar(34));
                sb.Append("text;FMT=Delimited");
                sb.Append(Convert.ToChar(34));

                // adapt schema.ini to the correct filename
                string schemaIniPath = Path.Combine(filePath, "Schema.ini");
                string contents = string.Empty;
                using (StreamReader sr = new StreamReader(schemaIniPath))
                {
                    contents = sr.ReadToEnd();
                }

                contents = contents.Replace("RoutingTableTemplate.csv", filename);

                using (StreamWriter sw = new StreamWriter(schemaIniPath, false, Encoding.ASCII))
                {
                    sw.Write(contents);
                }                    

            }
            ts.TraceInformation("Connection to access file: {0}", sb.ToString());
            return new OleDbConnection(sb.ToString());
        }
        
        /// <summary>
        /// Runs routing table import/export
        /// </summary>
        /// <remarks>Implements the main routing table import from and export to excel tables</remarks>
        /// <returns>exit code to return to calling process. 0 if successful, 1 if parameters are wrong, 2 otherwise</returns>
        internal int Run()
        {
            Parser parser = new Parser(System.Environment.CommandLine, this);
            parser.Parse();

            if (this.CmdLineShowHelp)
            {
                System.Reflection.Assembly exe = System.Reflection.Assembly.GetExecutingAssembly();
                parser.ShowHelp(exe.GetModules()[0].Name + "\n\nImport routing records into SwyxWare database");
                return 1;
            }

            if (this.CmdLineImport && this.CmdLineExport)
            {
                ts.TraceEvent(TraceEventType.Error, 1, "Only /import or /export are allowed, not both.");
                return 1;
            }

            if (!this.CmdLineImport && !this.CmdLineExport)
            {
                ts.TraceEvent(TraceEventType.Error, 1, "Either /import or /export have to be specified.");
                return 1;
            }

            if (string.IsNullOrEmpty(this.CmdLineFileToProcess))
            {
                ts.TraceEvent(TraceEventType.Error, 1, "No file given to {0}.", this.CmdLineExport ? "export" : "import");
                return 1;
            }

            if (this.CmdLineExport)
            {
                return this.DoExport();
            }

            if (this.CmdLineImport)
            {
                if (!File.Exists(this.CmdLineFileToProcess))
                {
                    ts.TraceEvent(TraceEventType.Error, 1, "File not found: {0}.", this.CmdLineFileToProcess);
                    return 1;
                }

                return this.DoImport();
            }

            return 2;
        }

        /// <summary>
        /// Create time restriction dataset row values from given routing table record
        /// </summary>
        /// <param name="rrdsRow">routing record dataset row to fill</param>
        /// <param name="rr">routing table entry</param>
        private static void SetTimeRestrictions(RoutingRecordsDS.RoutingRecordsRow rrdsRow, RoutingTableAdminView1Entry rr)
        {
            short startMinOfDay = rr.StartMinOfDay >= 1440 ? (short)1439 : rr.StartMinOfDay;
            short endMinOfDay = rr.EndMinOfDay >= 1440 ? (short)1439 : rr.EndMinOfDay;
            if (endMinOfDay <= startMinOfDay)
            {
                // invalid time range, ignore
                startMinOfDay = 0;
                endMinOfDay = 1440;
            }

            rrdsRow.StartTime = new DateTime(2008, 01, 01, startMinOfDay / 60, startMinOfDay % 60, 0).ToString("HH:mm", CultureInfo.InvariantCulture);
            rrdsRow.EndTime = new DateTime(2008, 01, 01, endMinOfDay / 60, endMinOfDay % 60, 0).ToString("HH:mm", CultureInfo.InvariantCulture);

            if ((rr.DayOfWeek & 0x02) != 0)
            {
                rrdsRow.Sundays = "1";
            }

            if ((rr.DayOfWeek & 0x04) != 0)
            {
                rrdsRow.Mondays = "1";
            }

            if ((rr.DayOfWeek & 0x08) != 0)
            {
                rrdsRow.Tuesdays = "1";
            }

            if ((rr.DayOfWeek & 0x10) != 0)
            {
                rrdsRow.Wednesdays = "1";
            }

            if ((rr.DayOfWeek & 0x20) != 0)
            {
                rrdsRow.Thursdays = "1";
            }

            if ((rr.DayOfWeek & 0x40) != 0)
            {
                rrdsRow.Fridays = "1";
            }

            if ((rr.DayOfWeek & 0x80) != 0)
            {
                rrdsRow.Saturdays = "1";
            }
        }

        /// <summary>
        /// Convert string to int.
        /// </summary>
        /// <param name="sourceValue">String to convert</param>
        /// <param name="defaultValue">default value to return when string cannot be converted</param>
        /// <param name="valueName">value name for tracing</param>
        /// <returns>int value parsed from given string</returns>
        private static int ConvertToInt(string sourceValue, int defaultValue, string valueName)
        {
            try
            {
                return int.Parse(sourceValue, CultureInfo.InvariantCulture);
            }
            catch (ArgumentNullException)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "{0} NULL, use default {1}", valueName, defaultValue);
                return defaultValue;
            }
            catch (FormatException)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "{0} has invalid format, use default {1}", valueName, defaultValue);
                return defaultValue;
            }
            catch (OverflowException)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "{0} is out of range, use default {1}", valueName, defaultValue);
                return defaultValue;
            }
        }

        /// <summary>
        /// Convert string to short
        /// </summary>
        /// <param name="sourceValue">String to convert</param>
        /// <param name="defaultValue">default value to return when string cannot be converted</param>
        /// <param name="valueName">value name for tracing</param>
        /// <returns>short value parsed from given string</returns>
        private static short ConvertToShort(string sourceValue, short defaultValue, string valueName)
        {
            try
            {
                return short.Parse(sourceValue, CultureInfo.InvariantCulture);
            }
            catch (ArgumentNullException)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "{0} NULL, use default {1}", valueName, defaultValue);
                return defaultValue;
            }
            catch (FormatException)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "{0} has invalid format, use default {1}", valueName, defaultValue);
                return defaultValue;
            }
            catch (OverflowException)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "{0} is out of range, use default {1}", valueName, defaultValue);
                return defaultValue;
            }
        }

        /// <summary>
        /// Fill a routing records dataset row from a SwyxWare routing table entry
        /// </summary>
        /// <param name="rrdsRow">Routing records dataset row</param>
        /// <param name="rr">Routing table entry</param>
        private static void FillRoutingRecordsRow(RoutingRecordsDS.RoutingRecordsRow rrdsRow, RoutingTableAdminView1Entry rr)
        {
            // Dataset uses strings for all columns because Jet.OLEDB provider cannot handle excel datatypes correctly
            // we do our own conversion here
            rrdsRow.CBCPrefix = rr.Prefix;
            rrdsRow.Deny = !rr.GrantAccess ? "1" : "0";
            rrdsRow.Enabled = rr.Enabled ? "1" : "0";
            rrdsRow.Number = rr.NumberFilter;
            rrdsRow.NumRetries = rr.Retries.ToString(CultureInfo.InvariantCulture);
            rrdsRow.Priority = rr.Priority.ToString(CultureInfo.InvariantCulture);
            if (!rr.IsGroupRestrictionNameNull)
            {
                rrdsRow.SourceGroup = rr.GroupRestrictionName;
            }

            if (!rr.IsLocationRestrictionNameNull)
            {
                rrdsRow.SourceLocation = rr.LocationRestrictionName;
            }

            rrdsRow.SourceNumber = rr.SourceNumberRestriction;

            if (!rr.IsUserRestrictionNameNull)
            {
                rrdsRow.SourceUser = rr.UserRestrictionName;
            }

            SetTimeRestrictions(rrdsRow, rr);

            rrdsRow.TrunkGroup = rr.PortBundleName;
            rrdsRow.RoutingRecordID = rr.RoutedNumbersEntryID.ToString(CultureInfo.InvariantCulture);
        }

        /// <summary>
        /// Create time slice entry collection from the given routing record row
        /// </summary>
        /// <param name="rr">routing record whose timeslice entry collection to fill with time slice entries</param>
        /// <param name="row">routing record row defining time restrictions</param>
        private static void CreateTimeSliceEntries(ref RoutedNumberEntry rr, RoutingRecordsDS.RoutingRecordsRow row)
        {
            // convert start and end time to minutes        
            short startMinutes = ConvertToMinutes(row.StartTime, 0, false, "StartTime");
            short endMinutes = ConvertToMinutes(row.EndTime, 1440, true, "StartTime");

            // we need one entry per weekday defined in the row
            if (ConvertToBool(row.Mondays, "Mondays"))
            {
                TimeSliceEntry tse = CreateTimeSliceEntry(startMinutes, endMinutes, SDayOfWeek.Monday);
                rr.TimeSliceEntryCollection.Add(tse);
            }

            if (ConvertToBool(row.Tuesdays, "Tuesdays"))
            {
                TimeSliceEntry tse = CreateTimeSliceEntry(startMinutes, endMinutes, SDayOfWeek.Tuesday);
                rr.TimeSliceEntryCollection.Add(tse);
            }

            if (ConvertToBool(row.Wednesdays, "Wednesdays"))
            {
                TimeSliceEntry tse = CreateTimeSliceEntry(startMinutes, endMinutes, SDayOfWeek.Wednesday);
                rr.TimeSliceEntryCollection.Add(tse);
            }

            if (ConvertToBool(row.Thursdays, "Thursdays"))
            {
                TimeSliceEntry tse = CreateTimeSliceEntry(startMinutes, endMinutes, SDayOfWeek.Thursday);
                rr.TimeSliceEntryCollection.Add(tse);
            }

            if (ConvertToBool(row.Fridays, "Fridays"))
            {
                TimeSliceEntry tse = CreateTimeSliceEntry(startMinutes, endMinutes, SDayOfWeek.Friday);
                rr.TimeSliceEntryCollection.Add(tse);
            }

            if (ConvertToBool(row.Saturdays, "Saturdays"))
            {
                TimeSliceEntry tse = CreateTimeSliceEntry(startMinutes, endMinutes, SDayOfWeek.Saturday);
                rr.TimeSliceEntryCollection.Add(tse);
            }

            if (ConvertToBool(row.Sundays, "Sundays"))
            {
                TimeSliceEntry tse = CreateTimeSliceEntry(startMinutes, endMinutes, SDayOfWeek.Sunday);
                rr.TimeSliceEntryCollection.Add(tse);
            }
        }
        
        /// <summary>
        /// Add new timeslice entry to given routing record with the given parameters
        /// </summary>
        /// <param name="startMinutes">start of time slice in day minutes (0-1439)</param>
        /// <param name="endMinutes">end of time slice in day minutes (1-1440)</param>
        /// <param name="dayOfWeek">Day of week the time slice is valid</param>
        /// <returns>Time slice entry filles with the given parameter</returns>
        private static TimeSliceEntry CreateTimeSliceEntry(short startMinutes, short endMinutes, SDayOfWeek dayOfWeek)
        {
            TimeSliceEntry tse = new TimeSliceEntry();
            tse.SetDefaultValues();
            tse.StartMinOfDay = startMinutes;
            tse.EndMinOfDay = endMinutes;
            tse.DayOfWeek = dayOfWeek;
            return tse;
        }

        /// <summary>
        /// converts routing record row time value to minutes of day (to be used in CDS routing record time restriction)
        /// </summary>
        /// <param name="sourceValue">time value from routing record row, must be of format "HH:mm"</param>
        /// <param name="defaultValue">default to use when conversion fails</param>
        /// <param name="endvalue">true if target value is end of the interval</param>
        /// <param name="valueName">value name for tracing</param>
        /// <returns>number of day minutes</returns>
        private static short ConvertToMinutes(string sourceValue, short defaultValue, bool endvalue, string valueName)
        {
            try
            {
                DateTime d = DateTime.ParseExact(sourceValue, "HH:mm", null);

                // CDS expects the end value to be one minute after the defined value
                short minutes = (short)(d.Minute + d.Hour * 60 + (endvalue ? 1 : 0));
                if (minutes > 1439)
                {
                    minutes = 1440;
                }

                return minutes;
            }
            catch (ArgumentNullException)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "{0} NULL, use default {1}", valueName, defaultValue);
                return defaultValue;
            }
            catch (FormatException)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "{0} has invalid format, use default {1}", valueName, defaultValue);
                return defaultValue;
            }
        }

        /// <summary>
        /// Convert given sourcevalue (name) to the corresponding CDS ID.
        /// </summary>
        /// <param name="sourceValue">name to convert</param>
        /// <param name="lookup">lookup method to use</param>
        /// <param name="valueName">value name for tracing</param>
        /// <returns>CDS id of the given name</returns>
        private static int ConvertToCDSID(string sourceValue, LookupMethod lookup, string valueName)
        {
            if (string.IsNullOrEmpty(sourceValue))
            {
                return 0;
            }

            int id = lookup(sourceValue);
            if (id == 0)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "{0} '{1}' not found, value ignored.", valueName, sourceValue);
                return 0;
            }

            return id;
        }

        /// <summary>
        /// Convert string to bool
        /// </summary>
        /// <param name="sourceValue">string value to convert</param>
        /// <param name="valueName">value name for tracing</param>
        /// <returns>true if string is "1" or "true"</returns>
        private static bool ConvertToBool(string sourceValue, string valueName)
        {
            return (string.Compare(sourceValue, "1", StringComparison.OrdinalIgnoreCase) == 0 ||
                string.Compare(sourceValue, "true", StringComparison.OrdinalIgnoreCase) == 0);
        }

        /// <summary>
        /// Add a routing record to the trunk group for the given routing record dataset row
        /// </summary>
        /// <param name="trunkGroups">trunk group enum which trunk groups to add routing records to</param>
        /// <param name="row">Routing records dataset row</param>
        private void FillRoutingRecordObject(PortBundleEnum trunkGroups, RoutingRecordsDS.RoutingRecordsRow row)
        {
            // First we need to find the right trunk group
            int trunkGroupID = this.cdsTools.LookupTrunkGroupID(row.TrunkGroup.ToUpperInvariant());
            if (trunkGroupID == 0)
            {
                ts.TraceEvent(TraceEventType.Warning, 0, "Trunk group '{0}' not found, skipping record", row.TrunkGroup);
                return;
            }

            // get the routing record collection 
            PortBundleEntry trunk = trunkGroups.GetObjectByPrimaryKey(trunkGroupID);
            RoutedNumberEntryCollection routingRecords = trunk.RoutedNumberEntryCollection;

            // create new routing records
            RoutedNumberEntry rr = new RoutedNumberEntry();

            // fixed description for later identification of imported routing records
            rr.Description = "ImportRR";

            // Jet.OLEDB driver for excel does not handle data type correctly, we do our own conversion
            rr.Enabled = ConvertToBool(row.Enabled, "Enabled");
            rr.Priority = (short)ConvertToInt(row.Priority, 500, "Priority");
            rr.GrantAccess = !ConvertToBool(row.Deny, "Deny");
            rr.NumberFilter = row.IsNumberNull() ? "" : row.Number;
            if (!row.IsSourceNumberNull())
            {
                rr.SourceNumberRestriction = row.SourceNumber;
            }

            if (!row.IsSourceGroupNull())
            {
                rr.GroupRestriction = ConvertToCDSID(row.SourceGroup, this.cdsTools.LookupGroupID, "SourceGroup");
            }

            if (!row.IsSourceLocationNull())
            {
                rr.LocationRestriction = ConvertToCDSID(row.SourceLocation, this.cdsTools.LookupLocationID, "SourceLocation");
            }

            if (!row.IsSourceUserNull())
            {
                int id = ConvertToCDSID(row.SourceUser, this.cdsTools.LookupUserID, "SourceUser");
                if (id != 0)
                {
                    // source user restriction cannot be set together with group or location restriction. 
                    // user restriction wins 
                    rr.SetGroupRestrictionNull();
                    rr.SetLocationRestrictionNull();
                    rr.UserRestriction = id;
                }
            }

            CreateTimeSliceEntries(ref rr, row);
            rr.Prefix = row.IsCBCPrefixNull() ? "" : row.CBCPrefix;
            rr.Retries = ConvertToShort(row.NumRetries, 0, "NumRetries");
            rr.NoFurtherRetries = false;
            routingRecords.Add(rr);
            ts.TraceEvent(TraceEventType.Verbose, 0, "Adding record {0},{1},{2},{3}", trunk.Name, rr.Enabled, rr.GrantAccess, rr.NumberFilter);
        }

        /// <summary>
        /// Dispose(bool disposing) executes in two distinct scenarios.
        /// </summary>
        /// <param name="disposing">If disposing equals true, the method has been called directly
        /// or indirectly by a user's code. Managed and unmanaged resources
        /// can be disposed.
        /// If disposing equals false, the method has been called by the
        /// runtime from inside the finalizer and you should not reference
        /// other objects. Only unmanaged resources can be disposed.</param>
        private void Dispose(bool disposing)
        {
            // Check to see if Dispose has already been called.
            if (!this.disposed)
            {
                // If disposing equals true, dispose all managed
                // and unmanaged resources.
                if (disposing)
                {
                    // Dispose managed resources.
                    this.cdsTools.Dispose();
                    this.cdsTools = null;
                }

                // Note disposing has been done.
                this.disposed = true;
            }
        }

        /// <summary>
        /// Import routing records
        /// </summary>
        /// <returns>exit code to return to caller. Zero when successful</returns>
        private int DoImport()
        {
            this.cdsTools = new CDSTools();
            try
            {
                ts.TraceInformation("Connecting to '{0}'", (string.IsNullOrEmpty(this.CmdLineServerName) ? "localhost" : this.CmdLineServerName));

                this.cdsTools.ConnectToCDS(
                    this.CmdLineServerName,
                    this.CmdLineUsername,
                    this.CmdLinePassword);
            }
            catch (SBaseException ex)
            {
                ts.TraceEvent(TraceEventType.Error, 2, "Connection to CDS failed: Error details:\r\n{0}", ex.MessageSummary);
                return 2;
            }
            catch (Exception ex)
            {
                ts.TraceEvent(TraceEventType.Error, 2, "Connection to CDS failed. Error details:\r\n{0}", ex);
                return 2;
            }

            // open input  file 
            ts.TraceInformation("Importing {0}", this.CmdLineFileToProcess);

            using (OleDbConnection databaseConnection = GetConnection(this.CmdLineFileToProcess))
            {
                databaseConnection.Open();
                RoutingRecordsDS rrds = new RoutingRecordsDS();
                RoutingRecordsDSTableAdapters.RoutingRecordsTableAdapter rrdta =
                    new IpPbxImportRR.RoutingRecordsDSTableAdapters.RoutingRecordsTableAdapter();
                rrdta.Connection = databaseConnection;
                rrdta.ClearBeforeFill = true;
                if (string.Compare(Path.GetExtension(this.CmdLineFileToProcess), ".csv", StringComparison.OrdinalIgnoreCase) == 0)
                {
                    // for csv the table name needs to be the file name. Change SQL statements in 
                    // data adapter
                    rrdta.SetTableName(Path.GetFileName(this.CmdLineFileToProcess));
                }

                rrdta.Fill(rrds.RoutingRecords);

                ts.TraceInformation("Importing {0} routing records.", rrds.RoutingRecords.Count);

                int numRetries = 2;
                do
                {
                    try
                    {
                        ts.TraceInformation("Getting current routing records");
                        PortBundleEnum trunkGroups = this.cdsTools.TrunkGroupEnum;

                        if (this.CmdLineReplace)
                        {
                            ts.TraceInformation("Removing all existing records with description 'ImportRR*'.");
                            foreach (PortBundleEntry trunkGroup in trunkGroups.PrimaryCollection)
                            {
                                // retrieve routed numbers of the trunk
                                RoutedNumberEntryCollection rns = trunkGroup.RoutedNumberEntryCollection;

                                // remove every routing record with description "ImportRR"
                                RoutedNumberEntryCollection oldRoutingRecords = new RoutedNumberEntryCollection();
                                oldRoutingRecords.CopyFrom(rns);
                                rns.RemoveAll();
                                foreach (RoutedNumberEntry rn in oldRoutingRecords)
                                {
                                    if (!rn.Description.StartsWith("ImportRR", StringComparison.OrdinalIgnoreCase))
                                    {
                                        rns.Add(rn);
                                    }
                                }
                            }

                            // update trunk groups in database
                            ts.TraceInformation("Updating database.");
                            trunkGroups.Update();
                        }

                        ts.TraceInformation("Adding new records.");
                        for (int i = 0; i < rrds.RoutingRecords.Count; i++)
                        {
                            RoutingRecordsDS.RoutingRecordsRow row = (RoutingRecordsDS.RoutingRecordsRow)rrds.RoutingRecords.Rows[i];
                            this.FillRoutingRecordObject(trunkGroups, row);
                        }

                        // update trunk groups in database
                        ts.TraceInformation("Updating database.");
                        trunkGroups.Update();
                        numRetries = 0;
                    }
                    catch (SDBConcurrencyException ex)
                    {
                        ts.TraceEvent(TraceEventType.Warning, 0, "CDS concurrency exception, trying once more. Details: {0}", ex.MessageSummary);

                        // try once more
                        numRetries--;
                    }
                }
                while (numRetries > 0);
            }

            this.cdsTools.DisconnectFromCDS();
            return 0;
        }

        /// <summary>
        /// export routing table to excel file
        /// </summary>
        /// <returns>exit code to return to callig application. Zero if successful</returns>
        private int DoExport()
        {
            this.cdsTools = new CDSTools();
            try
            {
                this.cdsTools.ConnectToCDS(
                    this.CmdLineServerName,
                    this.CmdLineUsername,
                    this.CmdLinePassword);
            }
            catch (SBaseException ex)
            {
                ts.TraceEvent(TraceEventType.Error, 2, "Connection to CDS failed: Error details:\r\n{0}", ex.MessageSummary);
                return 2;
            }
            catch (Exception ex)
            {
                ts.TraceEvent(TraceEventType.Error, 2, "Connection to CDS failed. Error details:\r\n{0}", ex);
                return 2;
            }

            // create output file from template
            Assembly executingAssembly = Assembly.GetExecutingAssembly();
            string sourcePath = Path.GetDirectoryName(executingAssembly.Location);

            sourcePath = Path.Combine(sourcePath, Properties.Settings.Default.TemplateExcelFile);
            if (string.Compare(Path.GetExtension(this.CmdLineFileToProcess), ".xlsx", StringComparison.OrdinalIgnoreCase) == 0)
            {
                sourcePath = sourcePath.ToUpperInvariant().Replace(".XLS", ".XLSX");
            }

            if (string.Compare(Path.GetExtension(this.CmdLineFileToProcess), ".csv", StringComparison.OrdinalIgnoreCase) == 0)
            {
                sourcePath = sourcePath.ToUpperInvariant().Replace(".XLS", ".CSV");
            }

            ts.TraceInformation("Using template: {0} to create {1}", sourcePath, this.CmdLineFileToProcess);
            File.Copy(sourcePath, this.CmdLineFileToProcess, true);
           
            using (OleDbConnection databaseConnection = GetConnection(this.CmdLineFileToProcess))
            {
                databaseConnection.Open();

                RoutingRecordsDS rrds = new RoutingRecordsDS();
                RoutingRecordsDSTableAdapters.RoutingRecordsTableAdapter rrdta =
                    new IpPbxImportRR.RoutingRecordsDSTableAdapters.RoutingRecordsTableAdapter();
                rrdta.Connection = databaseConnection;
                rrdta.ClearBeforeFill = true;
                if (string.Compare(Path.GetExtension(this.CmdLineFileToProcess), ".csv", StringComparison.OrdinalIgnoreCase) == 0)
                {
                    // for csv the table name needs to be the file name. Change SQL statements in 
                    // data adapter
                    rrdta.SetTableName(Path.GetFileName(this.CmdLineFileToProcess));
                }

                // we use the routing table view form CDS admin facade, which is faster than enumerating trunk groups
                RoutingTableAdminView1EntrySortableCollection routingTable =
                    this.cdsTools.RoutingTableView.CreateSortableFilterCollection();
                routingTable.Sort(RoutingTableAdminView1EntrySortableCollection.SortableAttributes.NumberFilter);

                ts.TraceInformation("Exporting {0} routing records ", routingTable.Count);
                foreach (RoutingTableAdminView1Entry rr in routingTable)
                {
                    RoutingRecordsDS.RoutingRecordsRow rrdsRow = rrds.RoutingRecords.NewRoutingRecordsRow();
                    FillRoutingRecordsRow(rrdsRow, rr);
                    rrds.RoutingRecords.AddRoutingRecordsRow(rrdsRow);
                }

                rrdta.Update(rrds);
            }

            this.cdsTools.DisconnectFromCDS();
            return 0;
        }
    }
}
